package com.example.teemor.es_demo.repository.clickhouse;

import com.example.teemor.es_demo.entity.UserBehaviorLog;
import com.example.teemor.es_demo.entity.UserProfile;
import com.example.teemor.es_demo.entity.HotProduct;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public class UserBehaviorRepository {

    private final JdbcTemplate clickhouseJdbcTemplate;

    public UserBehaviorRepository(@Qualifier("clickhouseJdbcTemplate") JdbcTemplate clickhouseJdbcTemplate) {
        this.clickhouseJdbcTemplate = clickhouseJdbcTemplate;
    }

    /**
     * 保存用户行为日志
     */
    public void saveUserBehaviorLog(UserBehaviorLog log) {
        String sql = "INSERT INTO es_analytics.user_behavior_log " +
                "(id, user_id, session_id, event_type, product_id, category, search_keyword, " +
                "page_url, user_agent, ip_address, timestamp, date) VALUES " +
                "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        clickhouseJdbcTemplate.update(sql,
                log.getId(),
                log.getUserId(),
                log.getSessionId(),
                log.getEventType(),
                log.getProductId(),
                log.getCategory(),
                log.getSearchKeyword(),
                log.getPageUrl(),
                log.getUserAgent(),
                log.getIpAddress(),
                log.getTimestamp(),
                log.getDate()
        );
    }

    /**
     * 批量保存用户行为日志
     */
    public void batchSaveUserBehaviorLogs(List<UserBehaviorLog> logs) {
        String sql = "INSERT INTO es_analytics.user_behavior_log " +
                "(id, user_id, session_id, event_type, product_id, category, search_keyword, " +
                "page_url, user_agent, ip_address, timestamp, date) VALUES " +
                "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        clickhouseJdbcTemplate.batchUpdate(sql, logs, logs.size(), (ps, log) -> {
            ps.setLong(1, log.getId());
            ps.setLong(2, log.getUserId());
            ps.setString(3, log.getSessionId());
            ps.setString(4, log.getEventType());
            ps.setObject(5, log.getProductId());
            ps.setString(6, log.getCategory());
            ps.setString(7, log.getSearchKeyword());
            ps.setString(8, log.getPageUrl());
            ps.setString(9, log.getUserAgent());
            ps.setString(10, log.getIpAddress());
            ps.setObject(11, log.getTimestamp());
            ps.setObject(12, log.getDate());
        });
    }

    /**
     * 获取用户行为统计
     */
    public Map<String, Object> getUserBehaviorStats(Long userId, LocalDateTime startDate, LocalDateTime endDate) {
        String sql = "SELECT " +
                "COUNT(*) as total_events, " +
                "COUNT(DISTINCT session_id) as total_sessions, " +
                "COUNT(DISTINCT product_id) as viewed_products, " +
                "SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) as page_views, " +
                "SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) as clicks, " +
                "SUM(CASE WHEN event_type = 'search' THEN 1 ELSE 0 END) as searches, " +
                "SUM(CASE WHEN event_type = 'add_cart' THEN 1 ELSE 0 END) as add_carts, " +
                "SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchases " +
                "FROM es_analytics.user_behavior_log " +
                "WHERE user_id = ? AND timestamp BETWEEN ? AND ?";
        
        return clickhouseJdbcTemplate.queryForMap(sql, userId, startDate, endDate);
    }

    /**
     * 获取热门商品排行
     */
    public List<HotProduct> getHotProducts(int limit) {
        String sql = "SELECT " +
                "product_id, product_title, category, brand, " +
                "view_count, order_count, sales_amount, score, date, hour " +
                "FROM es_analytics.hot_products " +
                "WHERE date = today() " +
                "ORDER BY score DESC " +
                "LIMIT ?";
        
        return clickhouseJdbcTemplate.query(sql, new HotProductRowMapper(), limit);
    }

    /**
     * 获取用户画像
     */
    public UserProfile getUserProfile(Long userId) {
        String sql = "SELECT " +
                "user_id, total_orders, total_amount, favorite_category, " +
                "favorite_brand, last_order_time, avg_order_amount, updated_at " +
                "FROM es_analytics.user_profile " +
                "WHERE user_id = ?";
        
        List<UserProfile> profiles = clickhouseJdbcTemplate.query(sql, new UserProfileRowMapper(), userId);
        return profiles.isEmpty() ? null : profiles.get(0);
    }

    /**
     * 获取实时PV/UV统计
     */
    public Map<String, Object> getRealTimePvUv() {
        String sql = "SELECT " +
                "COUNT(*) as pv, " +
                "COUNT(DISTINCT user_id) as uv " +
                "FROM es_analytics.user_behavior_log " +
                "WHERE date = today()";
        
        return clickhouseJdbcTemplate.queryForMap(sql);
    }

    /**
     * 获取转化率统计
     */
    public Map<String, Object> getConversionRate(LocalDateTime startDate, LocalDateTime endDate) {
        String sql = "SELECT " +
                "COUNT(DISTINCT CASE WHEN event_type = 'view' THEN user_id END) as view_users, " +
                "COUNT(DISTINCT CASE WHEN event_type = 'add_cart' THEN user_id END) as cart_users, " +
                "COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchase_users " +
                "FROM es_analytics.user_behavior_log " +
                "WHERE timestamp BETWEEN ? AND ?";
        
        return clickhouseJdbcTemplate.queryForMap(sql, startDate, endDate);
    }

    // RowMapper implementations
    private static class HotProductRowMapper implements RowMapper<HotProduct> {
        @Override
        public HotProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
            HotProduct product = new HotProduct();
            product.setProductId(rs.getLong("product_id"));
            product.setProductTitle(rs.getString("product_title"));
            product.setCategory(rs.getString("category"));
            product.setBrand(rs.getString("brand"));
            product.setViewCount(rs.getLong("view_count"));
            product.setOrderCount(rs.getLong("order_count"));
            product.setSalesAmount(rs.getBigDecimal("sales_amount"));
            product.setScore(rs.getDouble("score"));
            product.setDate(rs.getTimestamp("date").toLocalDateTime());
            product.setHour(rs.getInt("hour"));
            return product;
        }
    }

    private static class UserProfileRowMapper implements RowMapper<UserProfile> {
        @Override
        public UserProfile mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserProfile profile = new UserProfile();
            profile.setUserId(rs.getLong("user_id"));
            profile.setTotalOrders(rs.getInt("total_orders"));
            profile.setTotalAmount(rs.getBigDecimal("total_amount"));
            profile.setFavoriteCategory(rs.getString("favorite_category"));
            profile.setFavoriteBrand(rs.getString("favorite_brand"));
            profile.setLastOrderTime(rs.getTimestamp("last_order_time").toLocalDateTime());
            profile.setAvgOrderAmount(rs.getBigDecimal("avg_order_amount"));
            profile.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
            return profile;
        }
    }
}